#!/usr/bin/python3
import datetime

import pymysql
import requests
import math
# import pytesseract
import urllib.request
# 导入PIL库
from PIL import Image, ImageFilter
from paddleocr import PaddleOCR, draw_ocr
import json

from domain.idcard import IdCardFront
from domain.pydbconfig import DBConfig
from domain.user import User


# This is a sample Python script.

# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.


# 定义全局变量
# 定义数据库配置
dbConfig = DBConfig('', '', '', '')
imageHttpPrefix = 'https://artas.oss-cn-hangzhou.aliyuncs.com'
filePathPrefix = 'D:/images/image_'


# 从网络读取图片
def read_image_by_http(url, fileName):
    print('正在下载：' + url)
    # 请求URL获取内容
    # opener = urllib.request.build_opener()
    # image_data = opener.open(url).read()
    image_data = requests.get(url).content
    with open(filePathPrefix + str(fileName) + ".jpg", 'wb') as f:
        f.write(image_data)
        print(f"已下载： {url}")
    # image = Image.open(r''.join(image_data))
    # # 保存图片
    # image.save("D:/image_" + str(fileName) + ".jpg")


# 连接MySQL数据库
def connect_mysql():
    # 打开数据库连接
    # print('建立数据库连接......')
    db = pymysql.connect(host=dbConfig.host, user=dbConfig.user, password=dbConfig.password, database=dbConfig.database)
    # print('已建立数据库连接======')
    return db


# 关闭数据库连接
def close_mysql(db):
    # 关闭数据库连接
    # print('关闭数据库连接......')
    db.close()
    # print('已关闭数据库连接======')


# 获取用户信息总行数
def get_user_total_row():
    # 建立数据库连接
    db = connect_mysql()
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    # SQL语句
    sql = ("SELECT count(*) FROM user_shiming WHERE areaType = 'CN'"
           " AND idCard REGEXP '^[1-9][0-9]{5}(19|20)[0-9]{2}(0[1-9]|1[0-2])(0[1-9]|[1-2][0-9]|3[0-1])[0-9]{3}[0-9xX]$'"
           " AND realName IS NOT NULL"
           " AND pic1 IS NOT NULL"
           " AND pic2 IS NOT NULL"
           " AND pic3 IS NOT NULL")
    print('执行数据库查询中......')
    cursor.execute(sql)
    result = cursor.fetchone()
    print("用户信息总行数：", result[0])
    # 关闭数据库连接
    close_mysql(db)
    # 返回记录行数
    return result[0]


# 分页获取用户信息
def find_users_by_page(page, pageSize):
    userList = []
    # 建立数据库连接
    db = connect_mysql()
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    # 使用 execute()  方法执行 SQL 查询
    # SQL语句
    sql = (("SELECT areaType, idCard, realName, pic1, pic2, pic3 FROM user_shiming WHERE areaType = 'CN'"
            " AND idCard REGEXP '^[1-9][0-9]{5}(19|20)[0-9]{2}(0[1-9]|1[0-2])(0[1-9]|[1-2][0-9]|3[0-1])[0-9]{3}[0-9xX]$'"
            " AND realName IS NOT NULL"
            " AND pic1 IS NOT NULL"
            " AND pic2 IS NOT NULL"
            " AND pic3 IS NOT NULL"
            " LIMIT %d, %d;") % ((page - 1) * pageSize, pageSize))
    print('执行数据库查询中......')
    cursor.execute(sql)
    # 获取所有记录列表
    results = cursor.fetchall()
    for row in results:
        areaType = row[0]
        idCard = row[1]
        realName = row[2]
        pic1 = row[3]
        pic2 = row[4]
        pic3 = row[5]
        userList.append(User(areaType, idCard, realName, pic1, pic2, pic3))
        # 打印结果
        # print("areaType=%s,idCard=%s,realName=%s,pic1=%s,pic2=%s,pic3=%s" % \
        #       (areaType, idCard, realName, pic1, pic2, pic3))
    # 关闭数据库连接
    close_mysql(db)
    # 返回用户列表
    return userList


# 更新用户信息
def update_user(idCard, verifyFlag, errorMessage):
    # 建立数据库连接
    db = connect_mysql()
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    # SQL 更新语句
    sql = ''
    if (verifyFlag == True):
        sql = "UPDATE user_shiming SET is_effective = 1, verify_status = 1 WHERE idCard = '%s'" % (idCard)
    if (verifyFlag != True):
        sql = "UPDATE user_shiming SET is_effective = 1, verify_status = -1, verify_message = '%s' WHERE idCard = '%s'" % (errorMessage, idCard)
    try:
        # 执行SQL语句
        print('执行数据库更新中......')
        cursor.execute(sql)
        # 提交到数据库执行
        print('执行数据库提交事务中......')
        db.commit()
    except:
        # 发生错误时回滚
        print('发生异常，执行数据库回滚事务中......')
        db.rollback()

    # 关闭数据库连接
    close_mysql(db)


# 处理用户信息
def handle_user():
    # 获取用户信息总行数
    userRows = get_user_total_row()
    # 计算总页数
    pageSize = 5
    totalPage = math.ceil(userRows / pageSize)
    # 定义当前页码
    page = 1
    while page <= totalPage:
        print("正在查询第" + str(page) + "页数据......")
        # 分页获取用户信息
        userList = find_users_by_page(page, pageSize)
        # 遍历用户列表
        for user in userList:
            print("areaType=%s,idCard=%s,realName=%s,pic1=%s,pic2=%s,pic3=%s" % \
                  (user.areaType, user.idCard, user.realName, user.pic1, user.pic2, user.pic3))
            print('用户处理前系统时间：' + str(datetime.datetime.now()))
            # 请求第一张网络图片
            read_image_by_http(imageHttpPrefix + user.pic1, 'pic1_' + user.idCard)
            # 请求第二张网络图片
            # read_image_by_http(imageHttpPrefix + user.pic2, 'pic2_' + user.idCard)
            # 请求第三张张网络图片
            # read_image_by_http(imageHttpPrefix + user.pic3, 'pic3_' + user.idCard)
            verifyFlag = True
            errorMessage = ''
            # 识别身份证正面
            idCardFront = None
            idCardFrontContent = ''
            idCardFrontJSON = None
            try:
                idCardFront = identifyIdCardFront(user.idCard)
                idCardFrontContent = idCardFront.run()
                idCardFrontJSON = json.loads(idCardFrontContent)
            except:
                print('捕获到识别身份证正面异常：' + user.idCard)
                verifyFlag = False
                errorMessage = '识别身份证正面异常'
            if (verifyFlag == True):
                print('校验前输出：' + idCardFrontContent)
                # print('校验前JSON输出：' + idCardFrontJSON)
                if (user.realName != idCardFrontJSON['result']['name']):
                    verifyFlag = False
                    errorMessage = '姓名不匹配'
                tempIdCard = str(user.idCard).upper()
                tempIdNumber = str(idCardFrontJSON['result']['idNumber']).upper()
                if (tempIdCard != tempIdNumber):
                    verifyFlag = False
                    errorMessage = '身份证号码不匹配'
            if (verifyFlag == True):
                print('用户信息核实成功，正在写入数据库标记：' + user.idCard)
                update_user(user.idCard, verifyFlag, errorMessage)
            if (verifyFlag != True):
                print('用户信息校验失败，正在写入数据库标记：' + user.idCard + '，' + errorMessage)
                update_user(user.idCard, verifyFlag, errorMessage)
            print('用户处理后系统时间：' + str(datetime.datetime.now()))

        page = page + 1


# 匹配校验
# def match_check_old():
#     # 获取身份证第一张图片
#     pic1 = filePathPrefix + "pic1_52262319770122561x" + ".jpg"
#     print('正在加载图片：' + pic1)
#     image1 = Image.open(pic1)
#     print('正在识别图片：' + pic1)
#     # image1.show()
#     config = ('-l chi_sim --oem 1')
#     # text = pytesseract.image_to_string(image1, lang='chi_sim')
#     text = pytesseract.image_to_string(image1, config=config)
#     print('打印识别内容：' + text)
#     # print(("打印识别内容：%s") % (text))


# 识别身份证正面
def identifyIdCardFront(idCard):
    # 获取身份证第一张图片
    pic1 = filePathPrefix + "pic1_" + idCard + ".jpg"
    ocr = PaddleOCR(use_angle_cls=True, lang="ch")
    result = ocr.ocr(pic1, cls=True)
    txtArr = []
    for line in result[0]:
        txt = line[1][0]
        # 发现朝鲜文、彝文的身份证
        if (("姓" in txt and "性" in txt and "住" in txt) or ("名" in txt and "别" in txt and "生" in txt)) and line[1][
            1] < 0.75:
            continue
        else:
            txtArr.append(txt)
    idCardFront = IdCardFront(txtArr)
    print('打印身份证正面信息：' + idCardFront.run())
    return idCardFront


# 识别身份证反面
def identifyIdCardOpposite(idCard):
    # 获取身份证第二张图片
    pic1 = filePathPrefix + "pic2_" + idCard + ".jpg"
    ocr = PaddleOCR(use_angle_cls=True, lang="ch")
    result = ocr.ocr(pic1, cls=True)
    for idx in range(len(result)):
        res = result[idx]
        for line in res:
            print(line[1][0])


# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    print('开始处理用户数据中...................................................................................')
    handle_user()
    print('完成处理用户数据=====================================================================================')

# See PyCharm help at https://www.jetbrains.com/help/pycharm/
